package com.ustcinfo.study.hive.r5.pengqiang;

import java.sql.*;

/**
 * @Auther: pengqiang
 * @Date: 2018/8/3 20:13
 * @Description: hive作业,创建表dns_pengqiang,将/home/data中的数据导入表,然后统计每个域名的IP个数.
 */
public class HiveWork {
    // hive链接地址
    private static String url = "jdbc:hive2://nodex:10000/default";
    //hive用户名
    private static String userName = "root";
    // hive密码
    private static String passwd = "";
    // hive 驱动
    private static String driver = "org.apache.hive.jdbc.HiveDriver";
    private static Connection connection = null;
    private static Statement statement = null;
    private static ResultSet resultSet = null;
    // 查询tables sql
    private static String showTableSql = "show tables";
    // 创建外部表dns_pengqiang sql
    private static String createSql = "create external table dns_pengqiang(ip string, domain_name string, access_time string, target_ip string, status string)row format delimited fields terminated by '#' ";
    // 加载数据到dns_pengqiang sql
    private static String localDataSql = "load data local inpath '/home/data/dnsSample' into table dns_pengqiang ";
    // 统计出dns_pengqiang表中每个
    private static String countSql = "select domain_name,count(*) num from dns_pengqiang group by domain_name ORDER BY num DESC  ";
    // 查询dns_pengqiang表 sql
    private static String selectSql = "select * from dns_pengqiang ";

    /**
     * hive初始化方法
     */
    public static void init() {
        try {
            Class.forName(driver); //加载数据库驱动
            connection = DriverManager.getConnection(url, userName, passwd);   // 连接hive
            if (connection != null) {
                System.out.println("hive 连接成功");
            } else {
                System.out.println("hive 连接失败");
                System.exit(1);
            }
            statement = connection.createStatement();  //根据数据库连接，获取statement
        } catch (SQLException e) {
            e.printStackTrace();
            System.exit(1);
        }catch (ClassNotFoundException e) {
            System.out.println("驱动加载失败");
            e.printStackTrace();
        }
    }

    /**
     * hive关闭方法
     * @throws SQLException
     */
    public static void close() throws SQLException {
            if (statement != null) {  //由于statement由connection创建，所以需要先关闭statement再关闭connection，不然会报错
                statement.close();
            }
            if(connection != null) { //关闭数据库连接
                connection.close();
            }
            if (resultSet != null) {
                resultSet.close();
        }
    }

    /**
     * 列出表中所有数据
     * @throws SQLException
     */
    public static void selectAll() throws SQLException {
        resultSet = statement.executeQuery(selectSql);
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1) + "\t" +resultSet.getString(2) + "\t"
                    +resultSet.getString(3) + "\t" +resultSet.getString(4) + "\t" + resultSet.getInt(5));
        }
    }
    /**
     * 创建表dns_pengqiang
     * @throws SQLException
     */
    public static void createTable() throws SQLException{
        statement.execute(createSql);  //建表sql
    }

    /**
     * 展示所有表
     * @throws SQLException
     */
    public static void showTables() throws SQLException{
        // 获取数据库中所有的表
        resultSet = statement.executeQuery(showTableSql);
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1));
        }
    }

    /**
     * 读取/home/data/dnsSample 进表
     * @throws SQLException
     */
    public static void readData() throws SQLException{
        statement.execute(localDataSql);  //读取数据sql
    }

    /**
     * 根据dns列统计每个dns列的数量，并且根据dns数量降序排序
     * @throws SQLException
     */
    public static void countDns() throws SQLException{
        resultSet = statement.executeQuery(countSql);  //域名统计sql
        while (resultSet.next()) {
            System.out.println(resultSet.getString(1) + "\t" + resultSet.getInt(2));
        }
    }

    public static void main(String[] args) throws SQLException {
        init();//初始化连接

        createTable();//建表

        readData();//插入数据到表

        selectAll();//查询所有数据

        countDns();//dns统计

        close();//关闭连接
        }
    }
